from constant import Constant

class SyncCodeGenerator:
    def __init__(self, sync_strategy):
        self.sync_strategy = sync_strategy
        self.sync_sql = self.sync_strategy.sync_sql

    def gen_sync_code(self, data_cfg):
        """
        生成同步代码
        :return: 同步代码字符串
        """

        if data_cfg[Constant.SYNC_STRATEGY] == Constant.MERGE:
            # 合并同步代码
            merge_statement = self.__gen_merge_statement(
                data_cfg[Constant.TGT_TABLE],
                data_cfg[Constant.SRC_TABLE],
                data_cfg[Constant.CONV_COLUMNS],
                data_cfg[Constant.CONV_COLUMNS],
                data_cfg[Constant.CONV_COLUMNS],
                data_cfg[Constant.CONV_COLUMNS],
                data_cfg[Constant.TGT_TABLE_FILTER],
                data_cfg[Constant.DB_LINK]
            )
            if not merge_statement['success']:
                return {"success": False, "error": f"合并同步代码生成失败: {merge_statement['error']}", "statement": None}
            
            self.sync_sql = merge_statement['statement']

        elif data_cfg[Constant.SYNC_STRATEGY] == Constant.DELETE:
            # 删除同步代码
            delete_statement = self.__gen_delete_statement(
                data_cfg[Constant.TGT_TABLE],
                data_cfg[Constant.TGT_TABLE_FILTER]
            )
            if not delete_statement['success']:
                return {"success": False, "error": f"删除同步代码生成失败: {delete_statement['error']}", "statement": None}
            self.sync_sql = delete_statement['statement']
        else:
            # 删除同步代码
            delete_statement = self.__gen_delete_statement(
                data_cfg[Constant.TGT_TABLE],
                data_cfg[Constant.TGT_TABLE_FILTER]
            )
            if not delete_statement['success']:
                return {"success": False, "error": f"删除同步代码生成失败: {delete_statement['error']}", "statement": None}
            self.sync_sql = delete_statement['statement']
            # 插入同步代码
            insert_statement = self.__gen_insert_statement(
                data_cfg[Constant.TGT_TABLE],
                data_cfg[Constant.SRC_TABLE],
                data_cfg[Constant.CONV_COLUMNS],
                data_cfg[Constant.CONV_COLUMNS],
                data_cfg[Constant.CONV_COLUMNS],
                data_cfg[Constant.CONV_COLUMNS],
                data_cfg[Constant.TGT_TABLE_FILTER],
                data_cfg[Constant.DB_LINK]
            )
            if not insert_statement['success']:
                return {"success": False, "error": f"合并同步代码生成失败: {insert_statement['error']}", "statement": None}
            self.sync_sql = insert_statement['statement']

        return self.sync_sql
    

    def __gen_insert_statement(table_a, table_b, columns_a, primary_keys_a, columns_b, primary_keys_b, filter_condition=None, dblink=None):
        """
        生成 SQL INSERT 语句
        
        参数:
            table_a: 目标表名
            table_b: 源表名
            columns_a: 目标表的列名列表
            primary_keys_a: 目标表的主键列名列表
            columns_b: 源表的列名列表
            primary_keys_b: 源表的主键列名列表
            filter_condition: 筛选条件，用于过滤表B的数据（可选）
            dblink: 数据库链接信息，用于表B是远程表的情况（可选）
        
        返回:
            字典，包含状态和结果信息
        """
        # 参数验证
        if not all([table_a, table_b, columns_a, columns_b]):
            return {"success": False, "error": "表名和列名列表不能为空", "statement": None}
        
        # 处理主键为空的情况
        if not primary_keys_a and not primary_keys_b:
            # 如果两个表的主键都为空，则认为所有列都是主键
            primary_keys_a = columns_a
            primary_keys_b = columns_b
        elif not primary_keys_a or not primary_keys_b:
            return {"success": False, "error": "一个表有主键而另一个表没有", "statement": None}
        
        # 验证主键数量和名称是否一致
        if len(primary_keys_a) != len(primary_keys_b):
            return {"success": False, "error": "两个表的主键数量不一致", "statement": None}
        
        # 检查主键兼容性：确保A的主键都是B的主键
        for pk_a in primary_keys_a:
            if pk_a not in columns_b:
                return {"success": False, "error": f"目标表的主键 '{pk_a}' 不在源表中", "statement": None}
        
        # 构建列映射关系
        column_mapping = {}
        for col_a in columns_a:
            if col_a in columns_b:
                column_mapping[col_a] = col_a
            else:
                return {"success": False, "error": f"目标表的列 '{col_a}' 不在源表中", "statement": None}
        
        # 验证主键映射
        for pk_a, pk_b in zip(primary_keys_a, primary_keys_b):
            if column_mapping.get(pk_a) != pk_b:
                return {"success": False, "error": f"主键映射不匹配 - 目标表 '{pk_a}' 映射到源表 '{pk_b}'", "statement": None}
        
        # 构建 INSERT 语句
        insert_parts = []
        
        # INSERT 头部，明确列出目标表列名
        columns_str = "(" + ", ".join(columns_a) + ")" if columns_a != columns_b else '' 
        insert_parts.append(f"INSERT INTO {table_a} {columns_str}")

        # VALUES 部分
        insert_parts.append("SELECT")
        
        # 源表列名列表，若两表列相同则用 *
        if columns_a == columns_b:
            insert_parts.append("  *")
        else:
            source_columns = [column_mapping[col_a] for col_a in columns_a]
            source_columns_str = ", ".join(source_columns)
            insert_parts.append(f"{source_columns_str}")
        
        # FROM 部分，处理 DBlink
        if dblink:
            # 使用 DBlink 格式的表名
            full_table_b = f"{table_b}@{dblink}"
            insert_parts.append(f"FROM {full_table_b}")
        else:
            # 常规表名处理
            insert_parts.append(f"FROM {table_b}")
        
        # WHERE 部分，处理筛选条件
        if filter_condition:
            insert_parts.append(f"WHERE {filter_condition}")
        
        # 添加分号结束语句
        insert_parts.append(";")
        
        # 组合完整语句
        statement = " ".join(insert_parts)
        
        # 返回成功结果
        return {"success": True, "error": None, "statement": statement}


        
    def __gen_merge_statement(table_a, table_b, columns_a, primary_keys_a, columns_b, primary_keys_b, filter_condition=None, dblink=None):
        """
        生成 SQL MERGE 语句
        
        参数:
            table_a: 目标表名
            table_b: 源表名
            columns_a: 目标表的列名列表
            primary_keys_a: 目标表的主键列名列表
            columns_b: 源表的列名列表
            primary_keys_b: 源表的主键列名列表
            filter_condition: 筛选条件，用于过滤表B的数据（可选）
            dblink: 数据库链接信息，用于表B是远程表的情况（可选）
        
        返回:
            字典，包含状态和结果信息
        """
        # 参数验证
        if not all([table_a, table_b, columns_a, columns_b]):
            return {"success": False, "error": "表名和列名列表不能为空", "statement": None}
        
        # 处理主键为空的情况
        if not primary_keys_a and not primary_keys_b:
            # 如果两个表的主键都为空，则认为所有列都是主键
            primary_keys_a = columns_a
            primary_keys_b = columns_b
        elif not primary_keys_a or not primary_keys_b:
            return {"success": False, "error": "一个表有主键而另一个表没有", "statement": None}
        
        # 验证主键数量和名称是否一致
        if len(primary_keys_a) != len(primary_keys_b):
            return {"success": False, "error": "两个表的主键数量不一致", "statement": None}
        
        # 检查主键兼容性：确保A的主键都是B的主键
        for pk_a in primary_keys_a:
            if pk_a not in columns_b:
                return {"success": False, "error": f"目标表的主键 '{pk_a}' 不在源表中", "statement": None}
        
        # 构建列映射关系
        column_mapping = {}
        for col_a in columns_a:
            if col_a in columns_b:
                column_mapping[col_a] = col_a
            else:
                return {"success": False, "error": f"目标表的列 '{col_a}' 不在源表中", "statement": None}
        
        # 验证主键映射
        for pk_a, pk_b in zip(primary_keys_a, primary_keys_b):
            if column_mapping.get(pk_a) != pk_b:
                return {"success": False, "error": f"主键映射不匹配 - 目标表 '{pk_a}' 映射到源表 '{pk_b}'", "statement": None}
        
        # 构建 MERGE 语句
        merge_parts = []
        
        # MERGE 头部
        merge_parts.append(f"MERGE INTO {table_a} A")
        
        # USING 部分，处理筛选条件和 DBlink
        if dblink:
            # 使用 DBlink 格式的表名
            full_table_b = f"{table_b}@{dblink}"
            
            if filter_condition:
                # 使用子查询并应用筛选条件
                column_list = ', '.join([f"B.{col}" for col in columns_b])
                merge_parts.append(f"USING (SELECT {column_list} FROM {full_table_b} WHERE {filter_condition}) B")
            else:
                # 直接使用源表（通过 DBlink）
                merge_parts.append(f"USING {full_table_b} B")
        else:
            # 常规表名处理
            if filter_condition:
                # 使用子查询并应用筛选条件
                column_list = ', '.join([f"B.{col}" for col in columns_b])
                merge_parts.append(f"USING (SELECT {column_list} FROM {table_b} WHERE {filter_condition}) B")
            else:
                # 直接使用源表
                merge_parts.append(f"USING {table_b} B")
        
        # ON 条件部分（主键匹配）
        on_conditions = []
        for pk_a, pk_b in zip(primary_keys_a, primary_keys_b):
            on_conditions.append(f"A.{pk_a} = B.{pk_b}")
        
        merge_parts.append(f"ON ({' AND '.join(on_conditions)})")
        
        # WHEN MATCHED 部分（更新操作）
        update_columns = []
        for col_a in columns_a:
            # 跳过主键列的更新
            if col_a not in primary_keys_a:
                col_b = column_mapping[col_a]
                update_columns.append(f"A.{col_a} = B.{col_b}")
        
        if update_columns:
            merge_parts.append("WHEN MATCHED THEN")
            merge_parts.append(f"  UPDATE SET {', '.join(update_columns)}")
        
        # WHEN NOT MATCHED 部分（插入操作）
        insert_columns = []
        values = []
        
        for col_a in columns_a:
            col_b = column_mapping[col_a]
            insert_columns.append(f"A.{col_a}")
            values.append(f"B.{col_b}")
        
        if insert_columns and values:
            merge_parts.append("WHEN NOT MATCHED THEN")
            merge_parts.append(f"  INSERT ({', '.join(insert_columns)})")
            merge_parts.append(f"  VALUES ({', '.join(values)})")
        
        # 添加分号结束语句
        merge_parts.append(";")
        
        # 组合完整语句
        statement = "\n".join(merge_parts).strip()
        
        # 返回成功结果
        return {"success": True, "error": None, "statement": statement}

    def __gen_delete_statement(table_a, filter_condition=None):
        """
        生成 SQL 删除语句
        
        参数:
            table_a: 目标表名
            filter_condition: 筛选条件，用于过滤要删除的数据（可选）
            mode: 删除模式，可选 'delete' 或 'truncate'
        
        返回:
            字典，包含状态和结果信息
        """
        # 参数验证
        if not table_a:
            return {"success": False, "error": "表名不能为空", "statement": None}
        
        # 处理 truncate 模式
        if len(filter_condition) > 0:
            # 警告：truncate 不支持 where 条件，会删除整个表的数据
            return {"success": True, "error": None, "statement": f"TRUNCATE TABLE {table_a}"}
        
        # 处理 delete 模式
        delete_parts = []
        delete_parts.append(f"DELETE FROM {table_a}")
        
        # 添加筛选条件
        if filter_condition:
            delete_parts.append(f"WHERE {filter_condition}")
        
        # 添加分号结束语句
        delete_parts.append(";")
        # 组合完整语句
        statement = "\n".join(delete_parts)
        
        # 返回成功结果
        return {"success": True, "error": None, "statement": statement}
